SQL中insert into select及update select 用法 | 您所在的位置:网站首页 › select into用法语句 › SQL中insert into select及update select 用法 |
1、INSERT INTO SELECT结合语法详解
语句从一个表复制数据,然后把数据插入到一个已存在的表中。 其基本的书写格式为: INSERT INTO table2 (c1,c2,c3...) SELECT c1,c2,c3... FROM table1 where ...注意: (1)要求目标表Table2和源表Table1必须存在,并且字段相应也必须存在 (2)注意Table2的主键约束,如果Table2有主键而且不为空,则 field1, field2…中必须包括主键 (3)注意语法,不要加values,和插入一条数据的sql混了 INSERT INTO SELECT的特殊用法:与dual表结合(dual表:存在的最小的工作表,永远只有一条记录) (1)、与单条插入的sql语句的等价形式 //val1,val2,val3是具体的值 INSERT INTO table2 (c1,c2,c3...) select val1,val2,val3... from dual //举例:等价于 INSERT INTO table2 (name,age,sex)values('ian','21','male') INSERT INTO table2 (name,age,sex) select 'ian','21','male' from dual(2)、insert插入sql的条件判断:若不存在则插入 INSERT INTO table2 (c1,c2,c3...) select val1,val2,val3... from dual where not exists(select * from table2 where c1 = 'val1') 2、通用update语法详解一般简单的update语法比较通用 语法: UPDATE table_name SET column1=value1, column2=value2, ... WHERE some_column = some_value;注:若不加where条件则是更新表中的所有数据, 故执行没有where子句的update要慎重再慎重。 实例: UPDATE subject SET name='英语', type='语文' WHERE id = 1; 3、MySQL update与select结合 第一种:语法: UPDATE table1 SET column1 = (SELECT column FROM table2 [WHERE condition]) WHERE table1.column2 = value;注:若不加where条件则是更新表中的所有数据, 故执行没有where子句的update要慎重再慎重。 实例: UPDATE tb_bookcase SET name = (SELECT bookname FROM tb_bookinfo WHERE tb_bookinfo.type = tb_bookcase.type AND tb_bookinfo.ord_date IN (SELECT MAX(ord_date) FROM tb_bookinfo)) WHERE tb_bookcase.subject = '学习'; 第二种:语法: UPDATE table1 inner/left/right join table2/(select columns from table3 [inner/left/right join on condition] [where conditions]) as t3 ON condition SET column1 = value1,column2 = value2,... [WHERE conditions];实例: 例1: UPDATE $table1 a INNER JOIN $table2 b ON a.user_id = b.user_id SET a.balance = a.balance + b.income,b.status= 1 WHERE b.id = 5 AND b.status = 0;例2: UPDATE A INNER JOIN (SELECT B.B1 as B1,B.B2 as B2,C.C1 as C1 from B LEFT JOIN C on B.B3=C.C3) as t ON A.A3=t.B1 set A.A1=t.B2, A.A2=t.C1; UPDATE tb_bookcase INNER JOIN (SELECT tb_bookinfo.rid as rid,tb_bookinfo.bookname,department.name LEFT JOIN department on tb_bookinfo.depid=department.id) as t ON tb_bookcase.id=t.rid set tb_bookcase.bookname=t.bookname, tb_bookcase.departname=t.name ; 4、Oracle update与select结合语法: UPDATE table1 alias SET (column_name,column_name ) = ( SELECT column_name, column_name FROM table2 WHERE table2.column_name = alias.column_name) [WHERE column_name = VALUE]注:若不加where条件则是更新表中的所有数据, 故执行没有where子句的update要慎重再慎重。 实例: 例1: UPDATE t_order t1 SET (ordername,orderprice) = (SELECT detailname,totalprice FROM t_detail WHERE t_detail.detailclasses = t1.classes) WHERE t1.orderid = 1例2: UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID) WHERE ID IN (SELECT B.ID FROM B WHERE A.ID = B.ID) 5、SQLServer update与select结合语法: UPDATE table1 SET column1 = t2.column1, column1 = t2.column2, ... FROM table1 INNER/LEFT/RIGHT JOIN table2 ON table1.column = table2.column [WHERE conditions]注:若不加where条件则是更新表中的所有数据, 故执行没有where子句的update要慎重再慎重。 实例: UPDATE A SET A1 = t2.B2 , A2 = t2.C1 FROM A INNER JOIN ( SELECT B.B1,B.B2,C.C1 FROM B left join C on B.B3 = C.C3) t2 ON A.A3 = t2.B1 WHERE A.A4 = 1; UPDATE t_abnormal_fee SET order_code = t2.order_code , return_fee = t2.express_fee FROM t_abnormal_fee INNER JOIN ( SELECT t_order.id,t_order.order_code,t_detail.express_fee FROM t_order left join t_detail on t_order.name = t_detail.name) t2 ON t_abnormal_fee.id = t2.id WHERE t_abnormal_fee.id = 1; |
今日新闻 |
推荐新闻 |
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 |